package com.bosch.in.rtp1.ept.util;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.bosch.in.rtp1.ept.constant.CommonConstants;
import com.bosch.in.rtp1.ept.constant.FinishRecipeConstant;
import com.bosch.in.rtp1.ept.constant.LotStatusColumnName;
import com.bosch.in.rtp1.ept.constant.QueryKeyWord;
import com.bosch.in.rtp1.ept.constant.ReleasedHoldParameter;
import com.bosch.in.rtp1.ept.model.dto.BoxPlotFactor;
import com.bosch.in.rtp1.ept.model.dto.DurationColorCon;
import com.bosch.in.rtp1.ept.model.dto.FshRcpChartFactors;
import com.bosch.in.rtp1.ept.model.dto.LotStatus;
import com.bosch.in.rtp1.ept.model.dto.RelHldChartFactor;
import com.bosch.in.rtp1.ept.model.dto.ReleasedHold;
import com.bosch.in.rtp1.ept.model.technical.Condition4FinishRecipe;
import com.bosch.in.rtp1.ept.model.technical.Condition4HldDurByMonthDiagram;
import com.bosch.in.rtp1.ept.model.technical.Condition4LotStatus;
import com.bosch.in.rtp1.ept.model.technical.Condition4RelHldDiagram;
import com.bosch.in.rtp1.ept.model.technical.Condition4ReleasedHold;


/**
 * Helper class used to export data to excel and pdf file
 * @author TPR1HC
 *
 */
public class ExporterUtility {

	private static final Logger LOGGER = Logger.getLogger(ExporterUtility.class);
	
	private static final int EXCEL_TITLE_ROW = 0;
	private static final int EXCEL_TITLE_CELL = 0;
	private static final String EXCEL_LOT_HEADER_ROW_RANGE = "A7:Y7";
	private static final String EXCEL_RELEASED_HEADER_ROW_RANGE = "A14:S14";
	
	private static final int EXCEL_DEPARTMENT_ROW = 2;
	private static final int EXCEL_DEPARTMENT_CELL = 1;
	private static final int EXCEL_PROD_AREA_CELL = 3;
	
	private static final int EXCEL_CUS_NAME_ROW = 3;
	private static final int EXCEL_CUS_NAME_CELL = 1;
	private static final int EXCEL_PRIORITY_CELL = 3;
	
	private static final int EXCEL_HOLD_CODE_ROW = 4;
	private static final int EXCEL_HOLD_CODE_CELL = 1;
	private static final int EXCEL_HOLD_REASON_CELL = 3;
	
	
	private static final int LOT_START_ODD_ROW = 7;
	private static final int LOT_START_EVEN_ROW = 8;
	
	// ReleasedHold excel sheet constants
	private static final int REL_HLD_VAL_CELL = 1;
	private static final int REL_HLD_DEPARTMENT_ROW = 2;
	private static final int REL_HLD_AREA_ROW = 3;
	
	private static final int REL_HLD_CUS_NAME_ROW = 4;
	private static final int REL_HLD_PRIO_ROW = 5;
	
	private static final int REL_HLD_HOLD_CODE_ROW = 6;
	private static final int REL_HLD_HOLD_REASON_ROW = 7;
	
	private static final int REL_HLD_HOLD_CODE_RECLASS_ROW = 8;
	private static final int REL_HLD_HOLD_REASON_RECLASS_ROW = 9;
	
	private static final int REL_HLD_END_FROM_ROW = 10;
	private static final int REL_HLD_END_TO_ROW = 11;
	
	/**
	 * Export LotStatus list to excel file according to given excel template file.
	 * @param lotStatuses	data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws IOException 
	 */
	public static Workbook exportLotStatusToExcel(List<LotStatus> lotStatuses, 
			InputStream is, Condition4LotStatus con4LotStatus, DurationColorCon durColorCon) throws IOException {
		// Read excel template file and create instance of Workbook
		HSSFWorkbook workbook = new HSSFWorkbook(is);
		//Get 1st Sheet
		HSSFSheet sheet = workbook.getSheetAt(0); 

		Date currentDate = Calendar.getInstance().getTime();
		SimpleDateFormat titleDFM = new SimpleDateFormat(" (" + CommonConstants.FULL_DATE_FORMAT + ")");
		SimpleDateFormat cellDFM = new SimpleDateFormat(CommonConstants.FULL_DATE_FORMAT);
		
		// Update Title of excel sheet which include time stamp of records
		Row row = sheet.getRow(EXCEL_TITLE_ROW); // get first row
		Cell cell = row.getCell(EXCEL_TITLE_CELL); // get title cell
		cell.setCellValue(LotStatusColumnName.MEMS_LOT_STATUS_TITLE + QueryKeyWord.BLANK 
				+ titleDFM.format(currentDate));
		
		// Update department
		row = sheet.getRow(EXCEL_DEPARTMENT_ROW);
		cell = row.getCell(EXCEL_DEPARTMENT_CELL); // get department cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4LotStatus.getDepartment()));
		
		// Update product area
		cell = row.getCell(EXCEL_PROD_AREA_CELL); // get product area cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4LotStatus.getProductArea()));
		
		// Update customer name
		row = sheet.getRow(EXCEL_CUS_NAME_ROW);
		cell = row.getCell(EXCEL_CUS_NAME_CELL); // get customer name cell
		cell.setCellValue(con4LotStatus.getCustomerName());
		
		// Update priority
		cell = row.getCell(EXCEL_PRIORITY_CELL); // get priority cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4LotStatus.getPriority()));
		
		// Update hold code
		row = sheet.getRow(EXCEL_HOLD_CODE_ROW);
		cell = row.getCell(EXCEL_HOLD_CODE_CELL); // get holde code cell
		cell.setCellValue(con4LotStatus.getHoldText_Codes());
		
		// Update hold reason
		cell = row.getCell(EXCEL_HOLD_REASON_CELL); // get hold reason cell
		cell.setCellValue(con4LotStatus.getHoldReason());
		
		
		int _ODD = LOT_START_ODD_ROW; // start odd row
		int _EVEN = LOT_START_EVEN_ROW; // start even row
		int r = LOT_START_ODD_ROW; // start row
		
		// Create red background color style 
	    HSSFCellStyle redStyle = (HSSFCellStyle) workbook.createCellStyle();
	    redStyle.cloneStyleFrom(sheet.getRow(LOT_START_ODD_ROW).getCell(0).getCellStyle());
	    redStyle.setFillForegroundColor(HSSFColor.RED.index);
	    redStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    
	    // Create red background color style 
	    HSSFCellStyle yellowStyle = (HSSFCellStyle) workbook.createCellStyle();
	    yellowStyle.cloneStyleFrom(sheet.getRow(LOT_START_ODD_ROW).getCell(0).getCellStyle());
	    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
	    yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    
		//Iterate through dataset and update the Excel report
		for (LotStatus lotStatus : lotStatuses) {
			if (r % 2 == 1) { // if current row is odd
				copyRowStyle(workbook, sheet, _ODD, r);
			} else { // if current row is even
				copyRowStyle(workbook, sheet, _EVEN, r);
			}
			
			row = sheet.getRow(r++); // get current row
		   
			int i = 0;
		    cell = row.getCell(i++); // get lotId cell
		    cell.setCellValue(lotStatus.getLotId());
		  
		    cell = row.getCell(i++); // get curMainQTY cell
		    cell.setCellValue(Integer.valueOf(lotStatus.getCurMainQTY()));
		  
		    cell = row.getCell(i++); // get partId cell
		    cell.setCellValue(lotStatus.getPartId());
		    
		    cell = row.getCell(i++); // get lotType cell
		    cell.setCellValue(lotStatus.getLotType());
		    
		    cell = row.getCell(i++); // get customerName cell
		    cell.setCellValue(lotStatus.getCustomerName());
		    
		    cell = row.getCell(i++); // get priority cell
		    cell.setCellValue(lotStatus.getPriority());
		    
		    cell = row.getCell(i++); // get state cell
		    cell.setCellValue(lotStatus.getState());
		    
		    // Copy style from previous cell in the same row
		    HSSFCellStyle previousStyle = (HSSFCellStyle) cell.getCellStyle();
		    
		    cell = row.getCell(i++); // get waittime cell
		    double waitTime = Math.round(lotStatus.getWaitTime() * 100.0)/100.0;
		       
		    formatColor(workbook, cell, redStyle, yellowStyle, previousStyle, waitTime, durColorCon);
		    cell.setCellValue(waitTime);
		    
		    cell = row.getCell(i++); // get x-plan cell
		    cell.setCellValue(Math.round(lotStatus.getX_plan() * 100.0)/100.0);
		    
		    cell = row.getCell(i++); // get x-rw cell
		    cell.setCellValue(Math.round(lotStatus.getX_rw() * 100.0)/100.0);
		    
		    cell = row.getCell(i++); // get x-rwh cell
		    cell.setCellValue(Math.round(lotStatus.getX_rwh() * 100.0)/100.0);
		    
		    cell = row.getCell(i++); // get lotComment cell
		    cell.setCellValue(lotStatus.getLotComment());
		    
		    cell = row.getCell(i++); // get holdCode cell
		    cell.setCellValue(lotStatus.getHoldCode());
		    
		    cell = row.getCell(i++); // get holdReason cell
		    cell.setCellValue(lotStatus.getHoldReason());
		    
		    cell = row.getCell(i++); // get prodArea cell
		    cell.setCellValue(lotStatus.getProdArea());
		    
		    cell = row.getCell(i++); // get location cell
		    cell.setCellValue(lotStatus.getLocation());
		    
		    cell = row.getCell(i++); // get curPrcdId cell
		    cell.setCellValue(lotStatus.getCurPrcdId());
		    
		    cell = row.getCell(i++); // get stage cell
		    cell.setCellValue(lotStatus.getStage());
		    
		    cell = row.getCell(i++); // get recipeId cell
		    cell.setCellValue(lotStatus.getRecipeId());
		    
		    cell = row.getCell(i++); // get recipe title cell
		    cell.setCellValue(StringUtility.convertToEmptyIfNull(lotStatus.getRecipeTitle()));
		    
		    cell = row.getCell(i++); // get eqpType cell
		    cell.setCellValue(lotStatus.getEqpType());
		    
		    cell = row.getCell(i++); // get equipId cell
		    cell.setCellValue(lotStatus.getEquipID());
		    
		    cell = row.getCell(i++); // get queueTime cell
		    cell.setCellValue(cellDFM.format(lotStatus.getQueueTime()));
		    
		    cell = row.getCell(i++); // get startTime cell
		    cell.setCellValue(cellDFM.format(lotStatus.getStartTime()));
		    
		    cell = row.getCell(i++); // get cr1Sap cell
		    cell.setCellValue(lotStatus.getCr1Sap());
		}
		
		// Set auto filter header row
		sheet.setAutoFilter(CellRangeAddress.valueOf(EXCEL_LOT_HEADER_ROW_RANGE));
		
		//sheet.createFreezePane(8,LOT_START_ODD_ROW);
		
		// Remove empty row
		if (lotStatuses.size() < 2) {
			row = sheet.getRow(_EVEN); // get empty row
			sheet.removeRow(row);
			
			// if there is no row
			if (lotStatuses.size() == 0) {
				row = sheet.getRow(_ODD); // get empty row
				sheet.removeRow(row);
			}
		}
		
		return workbook;
	}
	
	
	/**
	 * Export ReleasedHold list to excel file according to given excel template file.
	 * @param releasedHolds	data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws IOException 
	 */
	public static Workbook exportReleasedHoldToExcel(List<ReleasedHold> releasedHolds, 
			InputStream is, Condition4ReleasedHold con4ReleasedHold, DurationColorCon durColorCon) throws IOException {
		// Read excel template file and create instance of Workbook
		HSSFWorkbook workbook = new HSSFWorkbook(is);
		//Get 1st Sheet
		HSSFSheet sheet = workbook.getSheetAt(0); 

		Date currentDate = Calendar.getInstance().getTime();
		SimpleDateFormat titleDFM = new SimpleDateFormat(" (" + CommonConstants.FULL_DATE_FORMAT + ")");
		SimpleDateFormat cellDFM = new SimpleDateFormat(CommonConstants.FULL_DATE_FORMAT);
		SimpleDateFormat toDateFM = new SimpleDateFormat(CommonConstants.SHORT_DATE_FORMAT_FOR_REPORT);
		
		// Update Title of excel sheet which include time stamp of records
		Row row = sheet.getRow(EXCEL_TITLE_ROW); // get first row
		Cell cell = row.getCell(EXCEL_TITLE_CELL); // get title cell
		cell.setCellValue(cell.getStringCellValue() + QueryKeyWord.BLANK 
				+ titleDFM.format(currentDate));
		
		// Update department
		row = sheet.getRow(REL_HLD_DEPARTMENT_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get department cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4ReleasedHold.getDepartment()));
		
		// Update product area
		row = sheet.getRow(REL_HLD_AREA_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get product area cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4ReleasedHold.getProductArea()));
		
		// Update customer name
		row = sheet.getRow(REL_HLD_CUS_NAME_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get customer name cell
		cell.setCellValue(con4ReleasedHold.getCustomerName());
		
		// Update priority
		row = sheet.getRow(REL_HLD_PRIO_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get priority cell
		cell.setCellValue(StringUtility.convertToSelectAllIfNull(con4ReleasedHold.getPriority()));
		
		// Update hold code
		row = sheet.getRow(REL_HLD_HOLD_CODE_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get holde code cell
		cell.setCellValue(con4ReleasedHold.getHoldText_Codes());
		
		// Update hold reason
		row = sheet.getRow(REL_HLD_HOLD_REASON_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get hold reason cell
		cell.setCellValue(con4ReleasedHold.getHoldReason());
		
		// Update hold code reclassified
		row = sheet.getRow(REL_HLD_HOLD_CODE_RECLASS_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get hold code reclassified cell
		cell.setCellValue(con4ReleasedHold.getHoldReclasText_Codes());
		
		
		// Update hold comment reclassified
		row = sheet.getRow(REL_HLD_HOLD_REASON_RECLASS_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get hold comment reclassified cell
		cell.setCellValue(con4ReleasedHold.getHoldCommentReclassified());
		
		// Update hold end from
		row = sheet.getRow(REL_HLD_END_FROM_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get hold end from
		if (con4ReleasedHold.getHoldEndFrom() != null) {
			cell.setCellValue(cellDFM.format(con4ReleasedHold.getHoldEndFrom()));
		}
		
		// Update hold end from
		row = sheet.getRow(REL_HLD_END_TO_ROW);
		cell = row.getCell(REL_HLD_VAL_CELL); // get hold end to
		if (con4ReleasedHold.getHoldEndTo() != null) {
			cell.setCellValue(toDateFM.format(con4ReleasedHold.getHoldEndTo()) + " 24:00:00");
		}
		
		int _ODD = 15; // start odd row
		int _EVEN = 14; // start even row
		int r = 14; // start row
		
		// Create red background color style 
	    HSSFCellStyle redStyle = (HSSFCellStyle) workbook.createCellStyle();
	    redStyle.cloneStyleFrom(sheet.getRow(_EVEN).getCell(0).getCellStyle());
	    redStyle.setFillForegroundColor(HSSFColor.RED.index);
	    redStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    
	    // Create red background color style 
	    HSSFCellStyle yellowStyle = (HSSFCellStyle) workbook.createCellStyle();
	    yellowStyle.cloneStyleFrom(sheet.getRow(_EVEN).getCell(0).getCellStyle());
	    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
	    yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	    
		
		//Iterate through dataset and update the Excel report
		for (ReleasedHold releasedHold : releasedHolds) {
			if (r % 2 == 1) { // if current row is even
				copyRowStyle(workbook, sheet, _ODD, r);
			} else { // if current row is odd
				copyRowStyle(workbook, sheet, _EVEN, r);
			}
			
			row = sheet.getRow(r++); // get current row
		   
			int i = 0;
			cell = row.getCell(i++); // get lotId cell
		    cell.setCellValue(releasedHold.getLotId());
		  
		    // Copy style from previous cell in the same row
		    HSSFCellStyle previousStyle = (HSSFCellStyle) cell.getCellStyle();
		    
		    cell = row.getCell(i++); // get heldduration cell
		    double heldDuration = Math.round(releasedHold.getHeldDuration() * 100.0)/100.0;
		    cell.setCellValue(heldDuration);
		    
		    formatColor(workbook, cell, redStyle, yellowStyle, previousStyle, heldDuration, durColorCon);
		    
		    cell = row.getCell(i++); // get waferNumber cell
		    cell.setCellValue(releasedHold.getWaferNumber());
		  
		    cell = row.getCell(i++); // get partId cell
		    cell.setCellValue(releasedHold.getPartId());
		    
		    cell = row.getCell(i++); // get lotType cell
		    cell.setCellValue(releasedHold.getLotType());
		    
		    cell = row.getCell(i++); // get customerName cell
		    cell.setCellValue(releasedHold.getCustomerName());
		    
		    cell = row.getCell(i++); // get priority cell
		    cell.setCellValue(releasedHold.getMaxPriority());
		    
		    cell = row.getCell(i++); // get prodArea cell
		    cell.setCellValue(releasedHold.getProdArea());
		    
		    cell = row.getCell(i++); // get location cell
		    cell.setCellValue(releasedHold.getLocation());
		    
		    cell = row.getCell(i++); // get stage cell
		    cell.setCellValue(releasedHold.getStage());
		    
		    cell = row.getCell(i++); // get recipeId cell
		    cell.setCellValue(releasedHold.getRecipeId());
		    
		    cell = row.getCell(i++); // get recipe title cell
		    cell.setCellValue(releasedHold.getRecipeTitle());
		    
		    cell = row.getCell(i++); // get halt start cell
		    cell.setCellValue(cellDFM.format(releasedHold.getHaltStart()));
		    
		    cell = row.getCell(i++); // get halt end cell
		    cell.setCellValue(cellDFM.format(releasedHold.getHaltEnd()));
		    
		    cell = row.getCell(i++); // get holdCode cell
		    cell.setCellValue(releasedHold.getHoldCode());
		    
		    cell = row.getCell(i++); // get holdComment cell
		    cell.setCellValue(releasedHold.getHoldComment());
		    
		    cell = row.getCell(i++); // get releasedComment cell
		    cell.setCellValue(releasedHold.getReleasedComment());
		    
		    cell = row.getCell(i++); // get hold code reclassified cell
		    cell.setCellValue(releasedHold.getHoldCodeReclassified());
		    
		    cell = row.getCell(i++); // get hold comment reclassified cell
		    cell.setCellValue(releasedHold.getHoldCommentReclassified());
		    
		}
		
		// Set auto filter header row
		sheet.setAutoFilter(CellRangeAddress.valueOf(EXCEL_RELEASED_HEADER_ROW_RANGE));
		
		// Remove empty row
		if (releasedHolds.size() < 2) {
			row = sheet.getRow(_ODD); // get empty row
			sheet.removeRow(row);
			
			// if there is no row
			if (releasedHolds.size() == 0) {
				row = sheet.getRow(_EVEN); // get empty row
				sheet.removeRow(row);
			}
		}
		
		return workbook;
	}

	/**
	 * Export FinishedRecipe list to excel file according to given excel template file.
	 * @param request		data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws ParsePropertyException, InvalidFormatException, IOException
	 */
	public static HSSFWorkbook exportDataChart(List<FshRcpChartFactors> exFactors, InputStream is,Condition4FinishRecipe condition4FinishRecipe) throws ParsePropertyException, InvalidFormatException
	{
        Map<String, Object> beans = new HashMap<String, Object>();
        fillExList(exFactors,condition4FinishRecipe.getYear());
        // put values to beans
        beans.put("exfactor", exFactors);
        beans.put("priority", condition4FinishRecipe.getPriority());
        beans.put("stage",StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getStage()));
        beans.put("location", StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getLocation()));
        beans.put("eqptype", StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getEqpType()));
        beans.put("equipId", StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getEquipID()));
        beans.put("partId", StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getPartId()));
        beans.put("timestamp", Calendar.getInstance().getTime().toLocaleString());
        beans.put("prodArea", FinishRecipeConstant.PROD_AREA_LIST[condition4FinishRecipe.getProdArea()-1]);
        beans.put("year", condition4FinishRecipe.getYear());
        beans.put("customer", StringUtility.convertToSelectAllIfNull(condition4FinishRecipe.getCustomerName()));
        //export to excel template
        XLSTransformer transformer = new XLSTransformer();
        transformer.markAsFixedSizeCollection("exfactor");
        return (HSSFWorkbook) transformer.transformXLS(is, beans);
	}
	
	/**
	 * Export RelHldChartFactor list to excel file according to given excel template file.
	 * @param request		data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws ParsePropertyException, InvalidFormatException, IOException
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook exportDataChart(
			List<RelHldChartFactor> relHldChartFactors, InputStream is,
			Condition4RelHldDiagram condition4RelHldDiagram) throws ParsePropertyException, InvalidFormatException {
		Map<String, Object> beans = new HashMap<String, Object>();
        // put values to beans
        beans.put("hold", relHldChartFactors);
        beans.put("year", condition4RelHldDiagram.getYear());
        beans.put("type", ReleasedHoldParameter.DIAGRAM_TYPE_LIST[condition4RelHldDiagram.getChartType()]);
        beans.put("timestamp", Calendar.getInstance().getTime().toLocaleString());
        //export to excel template
        XLSTransformer transformer = new XLSTransformer();
        transformer.markAsFixedSizeCollection("hold");
        return (HSSFWorkbook) transformer.transformXLS(is, beans);
	}

	/**
	 * Export BoxPlotFactor list to excel file according to given excel template file.
	 * @param request		data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws ParsePropertyException, InvalidFormatException, IOException
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook exportDataChart(List<BoxPlotFactor> bFactors,
			InputStream is, Condition4HldDurByMonthDiagram cByMonthDiagram) throws ParsePropertyException, InvalidFormatException {
		Map<String, Object> beans = new HashMap<String, Object>();
        // put values to beans
        beans.put("hold", bFactors);
        beans.put("year", cByMonthDiagram.getYear());
        beans.put("month",cByMonthDiagram.getMonth());
        beans.put("priority",cByMonthDiagram.getPriority());
        beans.put("timestamp", Calendar.getInstance().getTime().toLocaleString());
        //export to excel template
        XLSTransformer transformer = new XLSTransformer();
//        transformer.markAsFixedSizeCollection("hold");
		return (HSSFWorkbook) transformer.transformXLS(is, beans);
	}
	
	/**
	 * Export BoxPlotFactor list to excel file according to given excel template file.
	 * @param request		data to export
	 * @param templateFile	pre-formatter template file
	 * @return
	 * @throws ParsePropertyException, InvalidFormatException, IOException
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook exportActHldDataChart(List<BoxPlotFactor> bFactors,
			InputStream is, String activeHoldCodes) throws ParsePropertyException, InvalidFormatException {
		Map<String, Object> beans = new HashMap<String, Object>();
        // put values to beans
		beans.put("holdCodes", activeHoldCodes);
		beans.put("hold", bFactors);
        beans.put("timestamp", Calendar.getInstance().getTime().toLocaleString());
        //export to excel template
        XLSTransformer transformer = new XLSTransformer();
//        transformer.markAsFixedSizeCollection("hold");
		return (HSSFWorkbook) transformer.transformXLS(is, beans);
	}
	
	public static void streamOutBiData(BufferedInputStream input, ServletOutputStream out)
			throws SQLException {
		
		byte[] rb = new byte[10 * 1024];
		int ch = 0;
		try {
			while ((ch = input.read(rb)) != -1) {
				out.write(rb, 0, ch); // Error is here
			}
			input.close();
			out.flush();
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/***********PRIVATE METHOD****************/
	
	/**
	 * 
	 * @param cell
	 * @param myStyle
	 * @param value
	 */
	private static void formatColor(HSSFWorkbook workbook, Cell cell, HSSFCellStyle redStyle,
			HSSFCellStyle yellowStyle, HSSFCellStyle previousStyle,  double value, DurationColorCon durColorCon) {
		try {
			HSSFCellStyle newStyle;
			// Highlight background if duration > 3Day or duration > 2Day
			if (checkColorCondition(value, durColorCon, true)) {
				newStyle = (HSSFCellStyle) workbook.createCellStyle();
				newStyle.cloneStyleFrom(redStyle);
				
			} else if (checkColorCondition(value, durColorCon, false)) {
				newStyle = (HSSFCellStyle) workbook.createCellStyle();
				newStyle.cloneStyleFrom(yellowStyle);
				
			} else {
				newStyle = (HSSFCellStyle) workbook.createCellStyle();
				newStyle.cloneStyleFrom(previousStyle);
				
			}
			cell.setCellStyle(newStyle);
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
		}
	}
	
	/**
	 * Refer class SkippedRecipeConstanst to map operator value list
	 * OPERATORS 	= {"",">","<",">=","<=","=","<>"}
	 * @param duration
	 * @param durColorCon
	 * @param redColor
	 * @return
	 */
	private static boolean checkColorCondition(double duration, 
			DurationColorCon durColorCon, boolean redColor) {
		boolean satisfy = false;
		int operator = 0;
		double value = 0;
		if (redColor) {
			operator = durColorCon.getRedColOp();
			value = durColorCon.getRedColVal();
		} else {
			operator = durColorCon.getYellowColOp();
			value = durColorCon.getYellowColVal();
		}
		
		switch (operator) {
		case 1:
			satisfy = duration > value;
			break;
		case 2:
			satisfy = duration < value;
			break;
		case 3:
			satisfy = duration >= value;
			break;
		case 4:
			satisfy = duration <= value;
			break;
		case 5:
			satisfy = duration == value;
			break;
		case 6:
			satisfy = duration != value;
			break;
		default:
			break;
		}
		
		return satisfy;
	}
	
	
	/**
	 * Copy template predefined row style to new row style.
	 * @param workbook	excel file
	 * @param worksheet		first sheet in excel file
	 * @param sourceRowNum	template predefined row number
	 * @param destinationRowNum new row number where style is received from template row style
	 */
	private static void copyRowStyle(HSSFWorkbook workbook, HSSFSheet worksheet,
			int sourceRowNum, int destinationRowNum) {
		if (sourceRowNum != destinationRowNum) {
			// Get the source / new row
			HSSFRow newRow = worksheet.getRow(destinationRowNum);
			HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

			// If the row exist in destination, push down all rows by 1 else
			// create a new row
			if (newRow != null) {
				worksheet.shiftRows(destinationRowNum,
						worksheet.getLastRowNum(), 1);
			} else {
				newRow = worksheet.createRow(destinationRowNum);
			}

			// Loop through source columns to add to new row
			for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
				// Grab a copy of the old/new cell
				HSSFCell oldCell = sourceRow.getCell(i);
				HSSFCell newCell = newRow.createCell(i);

				// If the old cell is null jump to next cell
				if (oldCell == null) {
					newCell = null;
					continue;
				}

				// Copy style from old cell and apply to new cell
				newCell.setCellStyle(oldCell.getCellStyle());	
			}
		}
	}
	
	/**
	 * Fill List<FshRcpChartFactors> by adding one new FshRcpChartFactors object for each missing month
	 * @param exFactors
	 * @param year
	 */
	private static void fillExList(List<FshRcpChartFactors> exFactors, int year)
	{
		int month=0;
		int j=0;
		int curMonth;

		while (j<exFactors.size() && month<FinishRecipeConstant.NUM_OF_TIME_POINT)
		{
			curMonth = exFactors.get(j).getMonth();
			if (month<curMonth)
			{
				if (month>0)
				{
					exFactors.add(j, new FshRcpChartFactors(month,year));
				}
				else
				{
					exFactors.add(j, new FshRcpChartFactors(month,year-1));
				}
			}
			++j;
			++month;
		}
		
		if (month<FinishRecipeConstant.NUM_OF_TIME_POINT)
		{
			for (int i=month; i<FinishRecipeConstant.NUM_OF_TIME_POINT;++i)
			{
				if (i>0)
				{
					exFactors.add(j, new FshRcpChartFactors(i,year));
				}
				else
				{
					exFactors.add(j, new FshRcpChartFactors(i,year-1));
				}
				++j;
			}
		}
	}

}
